RootZanli & NinjaCr3

: / home / o5t6x7pgljbm / public_html / admin / app / V2 / BulkOperations / Importers /
Filename : BaseImporter.php
back
<?php

namespace App\V2\BulkOperations\Importers;

use App\Libraries\FileStorageSystem;
use App\Libraries\Helpers;
use App\Models\DataImport\ImportAdditionalDetail;
use App\Models\DataImport\ImportErrorSummary;
use App\Models\DataImport\ImportQueueItem;
use App\V2\BulkOperations\ImportFields\DataImportField;
use App\V2\BulkOperations\ImportServices\ImportLoggingService;
use App\V2\BulkOperations\ImportServices\TempTableService;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

abstract class BaseImporter
{
    protected $importProfile;
    protected $importQueueItem;
    protected $fileStorageSystem;
    protected $tempTableService;
    protected $importLogger;
    private $additional_fields = [];
    private $uploaded_file_namespace = 'uploaded_files';
    private $error_report_namespace = 'error_reports';
    protected $pk_column_name = 'id';
    protected $batch_size = 25000;

    public function __construct($importProfile)
    {
        $this->importProfile = $importProfile;
        $this->tempTableService = new TempTableService();
        $this->importLogger = new ImportLoggingService();
        //retriving singletone object of the FileStorageSystem.
        $this->fileStorageSystem = app(FileStorageSystem::class);
    }

    abstract protected function getMainTableId();
    abstract protected function getFields(): array;
    abstract protected function getProfileSpecificAdditionalFields(): array;

    private function populateAdditionalFields(){
        $this->additional_fields['validate_is_valid'] = new DataImportField('validate_is_valid','validate_is_valid','boolean', []);
        $this->additional_fields['validate_errors'] = new DataImportField('validate_errors','validate_errors','TEXT', []);
        $this->additional_fields['main_import_is_valid'] = new DataImportField('record_is_valid','record_is_valid','boolean', []);
        $this->additional_fields['main_import_errors'] = new DataImportField('record_errors','record_errors','TEXT', []);
        $this->additional_fields['created_at'] = new DataImportField('created_at','created_at','DATETIME', []);
        $this->additional_fields['updated_at'] = new DataImportField('updated_at','updated_at','TIMESTAMP', []);

        $this->additional_fields['validate_is_valid']->setDefaultValue('1');
    }
    protected function getAdditionalFields(): array
    {
        if(count($this->additional_fields) == 0){
            $this->populateAdditionalFields();
        }

        $profile_additional_fields = $this->getProfileSpecificAdditionalFields();

        return array_merge($this->additional_fields, $profile_additional_fields);
    }

    public function initiate($uploaded_file)
    {
        $logging_details = ['profile' => $this->importProfile->name, 'uploaded_file_name' => $uploaded_file->getClientOriginalName()];
        $this->importLogger->log("initiate import - START: " . print_r($logging_details, true));

        //storing file in local storage and creating import queue item
        $file = $this->fileStorageSystem->storeFile($uploaded_file, $this->uploaded_file_namespace);
        $this->importQueueItem = ImportQueueItem::create([
            'import_profile_id' => $this->importProfile->import_profile_id,
            'uploaded_file_name' => $file->original_file_name,
            'temp_table_name' => 'temp_' . uniqid(),
            'original_file_handle' => $file->file_handle,
            'current_status' => 'UPLOADED',
            'created_at' => now()
        ]);
        $fileInfo = Helpers::getCsvFileInfo($uploaded_file);
        $importAdditionalDetails = ImportAdditionalDetail::create([
            'import_profile_id' => $this->importQueueItem->import_profile_id,
            'import_queue_item_id' => $this->importQueueItem->import_queue_item_id,
            'total_records' => $fileInfo['rowCount'],
            'file_size_bytes' => $fileInfo['fileSize'],
            'created_at' => now(),
        ]);

        $logging_details['import_queue_item_id'] =  $this->importQueueItem->import_queue_item_id;
        $this->importLogger->log("initiate import for profile - END: " . print_r($logging_details, true));
        return $this->importQueueItem;
    }

    public function resumeImportQueueItem($import_queue_item_id){
        $this->importQueueItem = ImportQueueItem::where('import_queue_item_id', $import_queue_item_id)->first();
    }

    public function getSampleRecords(int $numRecords = 10): array
    {
        $filePath = storage_path( 'app/'. $this->fileStorageSystem->retrieveFilePath($this->importQueueItem->original_file_handle));
        $file = fopen($filePath, 'r');

        // Read the header line
        $header = fgetcsv($file);

        // Initialize an empty array to hold sample records
        $sampleRecords = [];

        // Get fields to map CSV data
        $fields = $this->getFields();

        // Read the first $numRecords lines
        for ($i = 0; $i < $numRecords; $i++) {
            $line = fgetcsv($file);
            if ($line === false) {
                break; // No more lines to read
            }

            $mappedRecord = [];
            foreach ($header as $index => $columnName) {
                if (isset($fields[$columnName])) {
                    $field = $fields[$columnName];
                    $mappedRecord[$field->getColumnName()] = [];
                    $mappedRecord[$field->getColumnName()]['value'] = $line[$index] ?? null;
                    $mappedRecord[$field->getColumnName()]['error'] = '';
                }
            }
            $field_keys = array_keys($fields);
            $orderedRecord = [];
            foreach ($field_keys as $key) {
                if($fields[$key]->isRequired()){
                    if(!isset($mappedRecord[$key])){
                        $orderedRecord[$key] = ['value' => null, 'error' => 'Required field not present'];
                    }
                }
                if (isset($mappedRecord[$key])) {
                    $validators = $fields[$key]->getValidators();
                    $errors = [];
                    foreach($validators AS $validator){
                        Log::debug("Executing validator: {$validator->getName()} for Field: {$fields[$key]->getColumnName()}");
                        $validation_result = $validator->validate( $fields[$key], $this->importQueueItem, $mappedRecord[$key]['value'], 'PREVIEW');
                        Log::debug("Validation Result: ". print_r($validation_result, true));
                        if(isset($validation_result['error']) && $validation_result['error']){
                            $errors[] = $validation_result['error_message'];
                        }
                    }
                    if(empty($errors)){
                        $orderedRecord[$key] = $mappedRecord[$key];
                    } else {
                        $orderedRecord[$key] = ['value' => $mappedRecord[$key]['value'], 'error' => implode(",", $errors)];
                    }
                }
            }

            $sampleRecords[] = $orderedRecord;
        }

        fclose($file);

        return $sampleRecords;
    }

    public final function getAllFields(){
        $additional_fields = $this->getAdditionalFields();
        $fields = $this->getFields();
        return array_merge($additional_fields, $fields);
    }

    public final function createTempTableAndValidate()
    {
        $logging_details = ['profile' => $this->importProfile->name,
            'import_queue_item_id' => $this->importQueueItem->import_queue_item_id,
            'temp_table_name' => $this->importQueueItem->temp_table_name];

        $additional_fields = $this->getAdditionalFields();
        $fields = $this->getFields();

        $this->importLogger->log("create temp table - START: " . print_r($logging_details, true));
        $this->tempTableService->createTempTable(array_merge($additional_fields, $fields), $this->importQueueItem->temp_table_name);
        $this->importLogger->log("create temp table - END: " . print_r($logging_details, true));

        $filePath = storage_path( 'app/'. $this->fileStorageSystem->retrieveFilePath($this->importQueueItem->original_file_handle));
        $this->importLogger->log("read csv and populate data in temp table - START: " . print_r($logging_details, true));
        $this->tempTableService->insertRecords($this->importQueueItem->temp_table_name, $filePath, $this->getFields());
        $this->importLogger->log("read csv and populate data in temp table - END: " . print_r($logging_details, true));

        $this->importLogger->log("validating the data - START: " . print_r($logging_details, true));
        $validate_response =  $this->validateData();
        $this->importLogger->log("validating the data - END: " . print_r($logging_details, true));

        return $validate_response;
    }

    private final function validateData()
    {
        // Validation logic
        $fields = $this->getFields();
        foreach($fields AS $field){
            $validators = $field->getValidators();
            foreach($validators AS $validator){
                // Log::debug("Executing validator: {$validator->getName()} for Field: {$field->getColumnName()}");
                $validation_result = $validator->validate($field, $this->importQueueItem, null, 'TEMP_TABLE');
                // Log::debug("Validation Result: ". print_r($validation_result, true));

            }
        }
        $this->validateProfileSpecificData();

        $this->postValidation();
    }

    protected abstract function validateProfileSpecificData();

    private function updatePostValidationSummary(){
        $counts = DB::table($this->importQueueItem->temp_table_name)
            ->selectRaw('
                SUM(CASE WHEN validate_is_valid = true THEN 1 ELSE 0 END) AS valid_records,
                SUM(CASE WHEN validate_is_valid = false THEN 1 ELSE 0 END) AS invalid_records,
                COUNT(*) AS total_records
            ')
            ->first();

        $this->importQueueItem->additionalDetails->update([
            'validation_failed_records' => $counts->invalid_records,
            'validation_passed_records' => $counts->valid_records,
        ]);
    }

    private function updatePostImportSummary(){
        $counts = DB::table($this->importQueueItem->temp_table_name)
            ->selectRaw("
                SUM(CASE WHEN {$this->getMainTableId()} IS NOT NULL THEN 1 ELSE 0 END) AS success_records,
                SUM(CASE WHEN {$this->getMainTableId()} IS NULL THEN 1 ELSE 0 END) AS failed_records,
                COUNT(*) AS total_records
            ")
            ->first();

        $this->importQueueItem->additionalDetails->update([
            'import_passed_records' => $counts->success_records,
            'import_failed_records' => $counts->failed_records,
        ]);
    }

    public final function postValidation(){
        $this->updatePostValidationSummary();
        $this->populateErrorSummary();
    }

    public final function importToMain()
    {
        $this->validateData();
        if ($this->importQueueItem->additionalDetails->validation_passed_records > 0) {
            // Insert into main tables
            $this->importLogger->log("Data imported to main table for profile: " . $this->importProfile->name);
            $this->importToMainProfileSpecificData();
            $this->postMainDbImport();
        } else {
            $this->importLogger->log("Data validation failed for profile: " . $this->importProfile->name);
        }
    }

    public abstract function importToMainProfileSpecificData();

    public final function postMainDbImport(){
        $this->updatePostImportSummary();
    }

    public function getImportQueueItem(){
        return $this->importQueueItem;
    }

    //Validation and Update Queries:
    protected function validateMobileDuplicate(){
        $field = $this->getAllFields()['mobile'];
        $record_is_valid_column_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $record_errors_column_name = $this->getAllFields()['validate_errors']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $error_message = "user already present with {$field->getColumnName()}";
        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " JOIN users AS u ";
        $validationQuery .= " ON u.mobile = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$record_is_valid_column_name} = false ";
        $validationQuery .= ", tmp.{$record_errors_column_name} = "
            . " IF(tmp.{$record_errors_column_name} IS NULL, '{$error_message}' ,"
            . " CONCAT( tmp.{$record_errors_column_name}, ', ', '{$error_message}') ) ";
        $validationQuery .= " WHERE u.deleted_at IS NULL ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    protected function validateEmailDuplicate(){
        $field = $this->getAllFields()['email'];
        $record_is_valid_column_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $record_errors_column_name = $this->getAllFields()['validate_errors']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $error_message = "user already present with {$field->getColumnName()}";
        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " JOIN users AS u ";
        $validationQuery .= " ON u.email = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$record_is_valid_column_name} = false ";
        $validationQuery .= ", tmp.{$record_errors_column_name} = "
            . " IF(tmp.{$record_errors_column_name} IS NULL, '{$error_message}' ,"
            . " CONCAT( tmp.{$record_errors_column_name}, ', ', '{$error_message}') ) ";
        $validationQuery .= " WHERE u.deleted_at IS NULL ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    protected function validateRole(){
        $field = $this->getAllFields()['user_role'];
        $record_is_valid_column_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $record_errors_column_name = $this->getAllFields()['validate_errors']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $error_message = "{$field->getColumnName()} not found";
        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN coins_for_college_roles AS r ";
        $validationQuery .= " ON r.role_name = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$record_is_valid_column_name} = false ";
        $validationQuery .= ", tmp.{$record_errors_column_name} = "
            . " IF(tmp.{$record_errors_column_name} IS NULL, '{$error_message}' ,"
            . " CONCAT( tmp.{$record_errors_column_name}, ', ', '{$error_message}') ) ";
        $validationQuery .= " WHERE r.coins_for_college_role_id IS NULL AND r.deleted_at IS NULL ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    protected function populateRoleId(){
        $field = $this->getAllFields()['user_role'];
        $ref_id_field = $this->getAllFields()['coins_for_college_role_id'];

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN coins_for_college_roles AS r ";
        $validationQuery .= " ON r.role_name = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$ref_id_field->getColumnName()} = r.{$ref_id_field->getColumnName()} ";
        $validationQuery .= " WHERE r.coins_for_college_role_id IS NOT NULL AND r.deleted_at IS NULL";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    protected function validateGoal(){
        $field = $this->getAllFields()['goal_name'];
        $record_is_valid_column_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $record_errors_column_name = $this->getAllFields()['validate_errors']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $error_message = "{$field->getColumnName()} not found";
        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN goals AS g ";
        $validationQuery .= " ON g.goal_name = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$record_is_valid_column_name} = false ";
        $validationQuery .= ", tmp.{$record_errors_column_name} = "
            . " IF(tmp.{$record_errors_column_name} IS NULL, '{$error_message}' ,"
            . " CONCAT( tmp.{$record_errors_column_name}, ', ', '{$error_message}') ) ";
        $validationQuery .= " WHERE g.goal_id IS NULL AND g.deleted_at IS NULL ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    protected function populateGoalId(){
        $field = $this->getAllFields()['goal_name'];
        $ref_id_field = $this->getAllFields()['goal_id'];

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN goals AS g ";
        $validationQuery .= " ON g.goal_name = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$ref_id_field->getColumnName()} = g.{$ref_id_field->getColumnName()} ";
        $validationQuery .= " WHERE g.goal_id IS NOT NULL AND g.deleted_at IS NULL";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    protected function validateProductCategory(){
        $field = $this->getAllFields()['product_category_name'];
        $record_is_valid_column_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $record_errors_column_name = $this->getAllFields()['validate_errors']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $error_message = "{$field->getColumnName()} not found";
        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN product_categories AS pg ";
        $validationQuery .= " ON pg.product_category_name = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$record_is_valid_column_name} = false ";
        $validationQuery .= ", tmp.{$record_errors_column_name} = "
            . " IF(tmp.{$record_errors_column_name} IS NULL, '{$error_message}' ,"
            . " CONCAT( tmp.{$record_errors_column_name}, ', ', '{$error_message}') ) ";
        $validationQuery .= " WHERE pg.product_category_id IS NULL AND pg.deleted_at IS NULL ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    protected function populateProductCategoryId(){
        $field = $this->getAllFields()['product_category_name'];
        $ref_id_field = $this->getAllFields()['product_category_id'];

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN product_categories AS pg ";
        $validationQuery .= " ON pg.product_category_name = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$ref_id_field->getColumnName()} = pg.{$ref_id_field->getColumnName()} ";
        $validationQuery .= " WHERE pg.product_category_id IS NOT NULL AND pg.deleted_at IS NULL";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    protected function validateGroupIfPresent(){
        $field = $this->getAllFields()['group_uuid'];
        $record_is_valid_column_name = $this->getAllFields()['validate_is_valid']->getColumnName();
        $record_errors_column_name = $this->getAllFields()['validate_errors']->getColumnName();

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $error_message = "{$field->getColumnName()} not found";
        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN `groups` AS g ";
        $validationQuery .= " ON g.uuid = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$record_is_valid_column_name} = false ";
        $validationQuery .= ", tmp.{$record_errors_column_name} = "
            . " IF(tmp.{$record_errors_column_name} IS NULL, '{$error_message}' ,"
            . " CONCAT( tmp.{$record_errors_column_name}, ', ', '{$error_message}') ) ";
        $validationQuery .= " WHERE tmp.{$field->getColumnName()} IS NOT NULL "
            . " AND g.group_id IS NULL AND g.deleted_at IS NULL";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    protected function populateGroupId(){
        $field = $this->getAllFields()['group_uuid'];
        $ref_id_field = $this->getAllFields()['group_id'];
        $team_ref_id_field = $this->getAllFields()['team_id'];

        $total_records = $this->importQueueItem->additionalDetails->total_records;

        $validationQuery = "UPDATE {$this->importQueueItem->temp_table_name} AS tmp ";
        $validationQuery .= " LEFT JOIN `groups` AS g  ";
        $validationQuery .= " ON g.uuid = tmp.{$field->getColumnName()} " ;
        $validationQuery .= " SET tmp.{$ref_id_field->getColumnName()} = g.{$ref_id_field->getColumnName()} ";
        $validationQuery .= " , tmp.{$team_ref_id_field->getColumnName()} = g.team_id ";
        $validationQuery .= " WHERE g.group_id IS NOT NULL AND g.deleted_at IS NULL ";

        Log::debug("Running Update Query: ". $validationQuery);
        $this->tempTableService->executeUpdateInBatches($validationQuery,
            'tmp.'.$this->pk_column_name, $this->batch_size, $total_records);
        return ['error' => false, 'error_message' => ''];
    }

    protected function populateErrorSummary(){
        // Fetch all validate_errors from the table
        $record_errors_column_name = $this->getAdditionalFields()['validate_errors']->getColumnName();
        $records = DB::table($this->importQueueItem->temp_table_name)
        ->select($record_errors_column_name)
        ->whereNotNull($record_errors_column_name)
        ->get();

        $errorCounts = [];

        foreach ($records as $record) {
            $errors = explode(',', $record->validate_errors);
            foreach ($errors as $error) {
                $error = trim($error);
                if ($error !== '') {
                    if (!isset($errorCounts[$error])) {
                        $errorCounts[$error] = 0;
                    }
                    $errorCounts[$error]++;
                }
            }
        }

        // Sort errors by count in descending order
        arsort($errorCounts);

        ImportErrorSummary::where('import_queue_item_id', $this->importQueueItem->import_queue_item_id)->delete();
        // Print or return the error counts
        foreach ($errorCounts as $error => $count) {
            ImportErrorSummary::create([
                'import_profile_id' => $this->importQueueItem->import_profile_id,
                'import_queue_item_id' => $this->importQueueItem->import_queue_item_id,
                'error_message' => $error,
                'error_record_count' => $count,
                'created_at' => now()
            ]);
        }
    }
}

?>